package banco.price;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import utilidades.FormatarData;
import banco.Banco;
import dominio.Cliente;
import dominio.price.Price;
import dominio.price.Tabela_Price;
import dominio.price.Totais;
import fachada.price.FachadaPrice;

public class PriceDAO implements FachadaPrice {

	/* (non-Javadoc)
	 * @see banco.price.FachadaPrice#insertPrice(dominio.price.Price, dominio.Cliente)
	 */
	@SuppressWarnings("finally")
	public void insertPrice(Price price, Cliente c) throws ClassNotFoundException, IOException {
	        
	        Connection conn = null;
	        PreparedStatement stmt = null;
	        ResultSet rs = null;
	        
	        try {
	            conn = Banco.getConexaoEXTRATOPAES();
	            stmt = conn.prepareStatement("insert into price values (?, ?, ?, ?, ?, ?, ?, ?)");
	            stmt.setFloat(1, price.getValor_emprestimo());
	            stmt.setString(2, price.getData_string());
	            stmt.setFloat(3, price.getTaxa_juros());
	            stmt.setInt(4, price.getQtd_prestacoes());
	            stmt.setString(5, price.getVencimento());
	            stmt.setString(6, price.getOutro());
	            stmt.setFloat(7, price.getPrestacao());
	            stmt.setString(8, c.getCnpj().trim());
	            stmt.executeUpdate();
	        }
	        catch (SQLException ex) {
	            ex.printStackTrace();
	        }
	        
	        catch (Exception e) {
	        	e.printStackTrace();
	        }
	        finally {
		        Banco.closeConexao(conn, rs, stmt);
	        }
	    }
	
	@SuppressWarnings("finally")
	public void insertTabela_Price(Price price, Tabela_Price tab) throws ClassNotFoundException, IOException {
	        
	        Connection conn = null;
	        PreparedStatement stmt = null;
	        ResultSet rs = null;
	        
	        try {
	            conn = Banco.getConexaoEXTRATOPAES();
	            stmt = conn.prepareStatement("insert into tabela_price values (?, ?, ?, ?, ?, ?, ?, ?)");
	            stmt.setString(1, tab.getData().trim());
	            stmt.setInt(2, tab.getNumero_parcela());
	            stmt.setFloat(3, tab.getSaldo_devedor_principal());
	            stmt.setFloat(4, tab.getJuros());
	            stmt.setFloat(5, tab.getAmortizacao());
	            stmt.setFloat(6, tab.getPrestacao());
	            stmt.setFloat(7, tab.getSaldo_devedor_geral());
	            stmt.setInt(8, price.getId());
	            stmt.executeUpdate();
	        }
	        catch (SQLException ex) {
	            ex.printStackTrace();
	        }
	        
	        catch (Exception e) {
	        	e.printStackTrace();
	        }
	        finally {
		        Banco.closeConexao(conn, rs, stmt);
	        }
	    }
	
	@SuppressWarnings("finally")
	public void insertTotais(Price price, Totais t) throws ClassNotFoundException, IOException {
	        
	        Connection conn = null;
	        PreparedStatement stmt = null;
	        ResultSet rs = null;
	        
	        try {
	            conn = Banco.getConexaoEXTRATOPAES();
	            stmt = conn.prepareStatement("insert into price_totais values (?, ?, ?, ?)");
	            stmt.setFloat(1, t.getTotal_juros());
	            stmt.setFloat(2, t.getTotal_amortizacao());
	            stmt.setFloat(3, t.getTotal_prestacao());
	            stmt.setInt(4, price.getId());
	            stmt.executeUpdate();
	        }
	        catch (SQLException ex) {
	            ex.printStackTrace();
	        }
	        
	        catch (Exception e) {
	        	e.printStackTrace();
	        }
	        finally {
		        Banco.closeConexao(conn, rs, stmt);
	        }
	    }

	@SuppressWarnings("finally")
	public Collection selectPrice_Cliente(Cliente c, boolean b) throws IOException {
		
		Connection conn = null;
	    PreparedStatement stmt = null;
	    ResultSet rs = null;
	    Price price = new Price();
	    Collection<Price> res = new ArrayList<Price>();
	    
	    try {
	        conn = Banco.getConexaoEXTRATOPAES();
	        stmt = conn.prepareStatement("select * from price where cnpj_cliente = ? and apropriada = ?");
	        stmt.setString(1, c.getCnpj().trim());
	        stmt.setBoolean(2, b);
	        rs = stmt.executeQuery();
	        while (rs.next()){
	        	price.setData_liberacao(FormatarData.inserirData(rs.getString("data_liberacao")));
	        	price.setData_string(rs.getString("data_liberacao"));
	        	price.setId(rs.getInt("id"));
	        	price.setOutro(rs.getString("outro"));
	        	price.setPrestacao(rs.getFloat("prestacao"));
	        	price.setQtd_prestacoes(rs.getInt("qtd_prestacoes"));
	        	price.setTaxa_juros(rs.getFloat("taxa_juros"));
	        	price.setValor_emprestimo(rs.getFloat("valor_emprestimo"));
	        	price.setVencimento(rs.getString("vencimento"));
	        	res.add(price);
	        }
	    }
	    catch (SQLException ex) {
	        ex.printStackTrace();
	    }
	    finally {
	        Banco.closeConexao(conn, rs, stmt);
	        return res;
	    }
	    
	}
	
	@SuppressWarnings("finally")
	public Collection selectTabela_Price(Price price) throws IOException {
		
		Connection conn = null;
	    PreparedStatement stmt = null;
	    ResultSet rs = null;
	    Collection<Tabela_Price> res = new ArrayList<Tabela_Price>();
	    
	    try {
	        conn = Banco.getConexaoEXTRATOPAES();
	        stmt = conn.prepareStatement("select * from tabela_price where id_price = ?");
	        stmt.setInt(1, price.getId());
	        rs = stmt.executeQuery();
	        while (rs.next()){
	        	Tabela_Price t = new Tabela_Price();
	        	t.setAmortizacao(rs.getFloat("amortizacao"));
	        	t.setData(rs.getString("data"));
	        	t.setJuros(rs.getFloat("juros"));
	        	t.setNumero_parcela(rs.getInt("numero_parcela"));
	        	t.setPrestacao(rs.getFloat("prestacao"));
	        	t.setSaldo_devedor_geral(rs.getFloat("saldo_devedor_geral"));
	        	t.setSaldo_devedor_principal(rs.getFloat("saldo_devedor_principal"));
	        	res.add(t);
	        }
	    }
	    catch (SQLException ex) {
	        ex.printStackTrace();
	    }
	    finally {
	        Banco.closeConexao(conn, rs, stmt);
	        return res;
	    }
	    
	}
	
	@SuppressWarnings("finally")
	public Collection selectTotais(Price price) throws IOException {
		
		Connection conn = null;
	    PreparedStatement stmt = null;
	    ResultSet rs = null;
	    Collection<Totais> res = new ArrayList<Totais>();
	    
	    try {
	        conn = Banco.getConexaoEXTRATOPAES();
	        stmt = conn.prepareStatement("select * from price_totais where id_price = ?");
	        stmt.setInt(1, price.getId());
	        rs = stmt.executeQuery();
	        while (rs.next()){
	        	Totais t = new Totais();
	        	t.setTotal_amortizacao(rs.getFloat("total_amortizacao"));
	        	t.setTotal_juros(rs.getFloat("total_juros"));
	        	t.setTotal_prestacao(rs.getFloat("total_prestacao"));
	        	res.add(t);
	        }
	    }
	    catch (SQLException ex) {
	        ex.printStackTrace();
	    }
	    finally {
	        Banco.closeConexao(conn, rs, stmt);
	        return res;
	    }
	    
	}
	
	@SuppressWarnings("finally")
	public Price selectUltimo_Price() throws IOException {
		
		Connection conn = null;
	    PreparedStatement stmt = null;
	    ResultSet rs = null;
	    Price price = new Price();
	    
	    try {
	        conn = Banco.getConexaoEXTRATOPAES();
	        stmt = conn.prepareStatement("select * from price order by id DESC limit 1");
	        rs = stmt.executeQuery();
	        while (rs.next()){
	        	price.setData_liberacao(FormatarData.inserirData(rs.getString("data_liberacao")));
	        	price.setData_string(rs.getString("data_liberacao"));
	        	price.setId(rs.getInt("id"));
	        	price.setOutro(rs.getString("outro"));
	        	price.setPrestacao(rs.getFloat("prestacao"));
	        	price.setQtd_prestacoes(rs.getInt("qtd_prestacoes"));
	        	price.setTaxa_juros(rs.getFloat("taxa_juros"));
	        	price.setValor_emprestimo(rs.getFloat("valor_emprestimo"));
	        	price.setVencimento(rs.getString("vencimento"));
	        }
	    }
	    catch (SQLException ex) {
	        ex.printStackTrace();
	    }
	    finally {
	        Banco.closeConexao(conn, rs, stmt);
	        return price;
	    }
	    
	}
	
	@SuppressWarnings("finally")
	public Price selectPriceById(int id) throws IOException {
		
		Connection conn = null;
	    PreparedStatement stmt = null;
	    ResultSet rs = null;
	    Price price = new Price();
	    
	    try {
	        conn = Banco.getConexaoEXTRATOPAES();
	        stmt = conn.prepareStatement("select * from price where id = ?");
	        stmt.setInt(1, id);
	        rs = stmt.executeQuery();
	        while (rs.next()){
	        	price.setData_liberacao(FormatarData.inserirData(rs.getString("data_liberacao")));
	        	price.setData_string(rs.getString("data_liberacao"));
	        	price.setId(rs.getInt("id"));
	        	price.setOutro(rs.getString("outro"));
	        	price.setPrestacao(rs.getFloat("prestacao"));
	        	price.setQtd_prestacoes(rs.getInt("qtd_prestacoes"));
	        	price.setTaxa_juros(rs.getFloat("taxa_juros"));
	        	price.setValor_emprestimo(rs.getFloat("valor_emprestimo"));
	        	price.setVencimento(rs.getString("vencimento"));
	        }
	    }
	    catch (SQLException ex) {
	        ex.printStackTrace();
	    }
	    finally {
	        Banco.closeConexao(conn, rs, stmt);
	        return price;
	    }
	    
	}
	
	@SuppressWarnings("finally")
	public void updatePrice(Price price) throws ClassNotFoundException, IOException {
	    
	    Connection conn = null;
	    PreparedStatement stmt = null;
	    ResultSet rs = null;	   
	    
	    try {
	        conn = Banco.getConexaoEXTRATOPAES();
	        stmt = conn.prepareStatement("update price set apropriada = ? where id = ?");
	        stmt.setBoolean(1, price.isApropriada());
	        stmt.setFloat(2, price.getId());
	        stmt.executeUpdate();
	    }
	    catch (SQLException ex) {
	        ex.printStackTrace();
	    }
	    
	    catch (Exception e) {
	    	e.printStackTrace();
	    }
	    finally {
	        Banco.closeConexao(conn, rs, stmt);
	    }
	}
	
}
